﻿CREATE proc [dbo].[sp_TimeBookingReport]
	@Year int 
as
SELECT  
	[Year] as [Year],
	[MonthName] as [Month Name],
	ResourceSoeid as [Resource SOE],
	ResourceName as [Resource Name],
	ResourceType as [Resource Type],
	ResourceManagerName as [Res Manager Name],
	ProjectNonProject as [Project/Non-Project],
	ProgramId as [Program Id],
	ProgramName as [Program Name],
	ProjectId as [Project Id],
	ProjectName as [Project Name],
	ProjectManager as [Project Manager],
	Program.ProgramInvestmentAlignmentCategory as [Program Investment Alignment Category],
	CASE SystemMSNodeName6
		WHEN 'Global Functions Technology Services [L6]' THEN 'GFTS'
		--WHEN 'ICG Technology [L6]' THEN 'ICG Tech'
		ELSE 'ICG Tech'
	END as [LOB Parent],
	CASE ProjectNonProject
		WHEN 'Non-Project' THEN 'Time booked in Non-Project'
		ELSE Program.ProgramOwner 
	END as [Program Business Relationship],
	KeyBusinessContact as [Key Business Contact],
	CASE ProjectNonProject
		WHEN 'Non-Project' THEN 'Time booked in Non-Project'
		ELSE TechnologyProgramManager 
	END as [Technology Program Manager],
	TechProgramManagerSMT as [Tech Program Manager SMT],
	TechProgramManagerSTLT as [Tech Program Manager STLT],
	SystemGOCCode as [System GOC Code],
	SystemManGeoTreeNode as [System Man Geo Tree Node],
	SystemMSNodeName3 as [System MS Node Name3],
	SystemMSNodeName4 as [System MS Node Name4],
	SystemMSNodeName6 as [System MS Node Name6],
	SystemMSNodeName7 as [System MS Node Name7],
	SystemMSNodeName8 as [System MS Node Name8],
	SystemMSNodeName9 as [System MS Node Name9],
	SystemMSNodeName10 as [System MS Node Name10],
	ClientGOCId as [Client GOC Id],
	ClientManGeoTreeNode as [Client Man Geo Tree Node],
	ClientMSNodeName3 as [Client MS Node Name3],
	ClientMSNodeName4 as [Client MS Node Name4],
	ClientMSNodeName5 as [Client MS Node Name5],
	ClientMSNodeName6 as [Client MS Node Name6],
	ClientMSNodeName7 as [Client MS Node Name7],
	ClientMSNodeName8 as [Client MS Node Name8],
	ClientMSNodeName9 as [Client MS Node Name9],
	ClientMSNodeName10 as [Client MS Node Name10],
	CASE ClientPercentage
		WHEN 0 THEN 100
		WHEN NULL THEN 100
		ElSE ISNULL(ClientPercentage, 100)
	END as [Client Percentage],
	MinHrsPerWeek as [Min Hrs Per Week],
	TimesheetStatus as [Timesheet Status],
	WeekStart as [Week Start],
	WeekEnd as [Week End],
	--TotalHours * ISNULL(ClientPercentage, 100) / 100 as [Hours Booked],
	TotalHours * CASE ClientPercentage
		WHEN 0 THEN 100
		WHEN NULL THEN 100
		ElSE ISNULL(ClientPercentage, 100)
	END / 100 as [Hours Booked],
	TotalHours as [Total Hours],
	[Month]
	--RIGHT('0' + CONVERT(varchar(2),DATEPART(MM, CAST([MonthName] + ' 1900' AS DATETIME))), 2) as [Month]
FROM TimeBooking
LEFT JOIN Project ON ProjectId = Project.Id
LEFT JOIN Program on ProgramId = Program.Id
WHERE [Year] = @Year
ORDER BY [Month], ResourceSoeid